package com.octopus.optiworks.tool;

import java.io.File;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.Map;

import com.octopus.optiworks.model.DataSheet;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CreateDataSheet0386RevA implements CreateDataSheetExcel{
	
	private static CreateDataSheet0386RevA createDataSheet0386RevA;
	
	private CreateDataSheet0386RevA(){
		
	}
	
	public static CreateDataSheet0386RevA getInstance(){
		if(createDataSheet0386RevA == null)
			createDataSheet0386RevA = new CreateDataSheet0386RevA();
		return createDataSheet0386RevA;
	}

	public void exportExcel(DataSheet datasheet, File template, OutputStream out) {
		try {
			WorkbookSettings settings = new WorkbookSettings ();
			settings.setWriteAccess(null);
			
			Workbook workbook = Workbook.getWorkbook(template); 
			WritableWorkbook wb = Workbook.createWorkbook(out, workbook, settings);

			
			WritableSheet ws=wb.getSheet("DWDM(单器件)");
			Label lable1=new Label(1,2,datasheet.getInternalPo());
			Label lable2=new Label(4,2,datasheet.getCustomerPo());
			Label lable3=new Label(1,3,datasheet.getInternalPn());
			Label lable4=new Label(4,3,datasheet.getCustomerPn());
			Label lable5=new Label(1,4,datasheet.getSn());
			Label lable6=new Label(2,5,datasheet.getItuChannel());
			Label lable7=new Label(2,6,datasheet.getChannelPassband());
			Label lable8=new Label(5,6,datasheet.getReflectedWavelength());
			Label lable9=new Label(2,7,datasheet.getPassbandInsertionLoss());
			Label lable10=new Label(5,7,datasheet.getReflectedInsertionLoss());
			Label lable11=new Label(2,8,datasheet.getPassbandRipple());
			Label lable12=new Label(5,8,datasheet.getReflectedRipple());
			Label lable13=new Label(2,9,datasheet.getIsolationAdjacentChannel());
			Label lable14=new Label(5,9,datasheet.getReflectedIsolation());
			Label lable15=new Label(2,10,datasheet.getIsolationNonAdjacentChannel());
			Label lable16=new Label(5,10,datasheet.getPdl());
			Label lable17=new Label(2,11,datasheet.getReturnLoss());
			Label lable18=new Label(5,11,datasheet.getDirectivity());
			Label lable19=new Label(2,12,datasheet.getPackageDimension());
			Label lable20=new Label(5,12,datasheet.getOperatingTemperature());
			Label lable21=new Label(2,13,datasheet.getFiberLength());
			Label lable22=new Label(2,14,datasheet.getConnectorType());
			Label lable23=new Label(2,15,datasheet.getFiberType());
			Label lable24=new Label(2,16,datasheet.getPortMark());
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm", new Locale("en"));
			Label lable25=new Label(0,17,"Date: "+ format.format(new Date()));
	
			ws.addCell(lable1);
			ws.addCell(lable2);
			ws.addCell(lable3);
			ws.addCell(lable4);
			ws.addCell(lable5);
			ws.addCell(lable6);
			ws.addCell(lable7);
			ws.addCell(lable8);
			ws.addCell(lable9);
			ws.addCell(lable10);
			ws.addCell(lable11);
			ws.addCell(lable12);
			ws.addCell(lable13);
			ws.addCell(lable14);
			ws.addCell(lable15);
			ws.addCell(lable16);
			ws.addCell(lable17);
			ws.addCell(lable18);
			ws.addCell(lable19);
			ws.addCell(lable20);
			ws.addCell(lable21);
			ws.addCell(lable22);
			ws.addCell(lable23);
			ws.addCell(lable24);
			ws.addCell(lable25);
			
			WritableCellFormat cellFormat0 = new WritableCellFormat();   
			cellFormat0.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);
			
			WritableCellFormat cellFormat1 = new WritableCellFormat();
			cellFormat1.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);
			cellFormat1.setBorder(Border.TOP, jxl.format.BorderLineStyle.MEDIUM);
			
			WritableCellFormat cellFormat2 = new WritableCellFormat();
			cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);
			cellFormat2.setBorder(Border.TOP, jxl.format.BorderLineStyle.MEDIUM);
			cellFormat2.setBorder(Border.RIGHT, jxl.format.BorderLineStyle.MEDIUM);
			
			WritableCellFormat cellFormat4 = new WritableCellFormat();
			cellFormat4.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN);
			cellFormat4.setBorder(Border.RIGHT, jxl.format.BorderLineStyle.MEDIUM);
			
			
			WritableCellFormat cellFormat25 = new WritableCellFormat();
			//cellFormat25.setBorder(Border.ALL, jxl.format.BorderLineStyle.MEDIUM);
			cellFormat25.setBorder(Border.TOP, jxl.format.BorderLineStyle.THIN);
			cellFormat25.setAlignment(jxl.format.Alignment.RIGHT);
			
			Label A1 = (Label)ws.getWritableCell(1,2);
			A1.setCellFormat(cellFormat1);
			A1 = (Label)ws.getWritableCell(4,2);
			A1.setCellFormat(cellFormat2);
			A1 = (Label)ws.getWritableCell(1,3);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(4,3);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(1,4);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,5);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,6);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,6);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,7);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,7);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,8);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,8);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,9);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,9);
			A1.setCellFormat(cellFormat4);
			
			A1 = (Label)ws.getWritableCell(2,9);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,9);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,10);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,10);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,11);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,11);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,12);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(5,12);
			A1.setCellFormat(cellFormat4);
			A1 = (Label)ws.getWritableCell(2,13);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(2,14);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(2,15);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(2,16);
			A1.setCellFormat(cellFormat0);
			A1 = (Label)ws.getWritableCell(0,17);
			A1.setCellFormat(cellFormat25);			
			
		
			
			wb.write();
			wb.close();
			
			workbook.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		

}
